#!/bin/bash
#设置任务异常退出
set -eo pipefail
dt='{{ execution_date | cst_ds }}'
name='退件率汇总'
# 在退出时清理文件
trap "rm -f /tmp/${name}_${dt}.zip /tmp/${name}_${dt}.csv && echo 'removed tmp files'" EXIT
# 创建txt文件
touch /tmp/${name}_${dt}.txt
#将中文字段名输入文件顶部
#将查询结果追加到文件中
{
  echo -e "派件日期\t大区名称\t代理区名称\t派件量\t退件量\t退件率\t">/tmp/${name}_${dt}.txt;
  spark-sql --driver-memory 8G --num-executors 8 --executor-memory 12g --executor-cores 6  --conf spark.executor.memoryOverhead=4g --conf spark.driver.maxResultSize=3g -S -e "
  with tmp_deliver as (
  select
   waybill_no
  ,regional_desc
  ,scan_site
  ,scan_site_code
  ,site_agent_code
  ,site_agent_name
  ,scan_time
  ,input_time
  from jms_dwd.dwd_tab_barscan_deliver_base_dt t1
  left join jms_dim.dim_network_whole_massage t2
         on site_agent_code = code
        and t2.network_type = 2
  where dt >= date_add('{{ execution_date | cst_ds }}',-7) and dt <= '{{ execution_date | cst_ds }}'
  and waybill_no is not null
  )
  , tmp_rolback as (
    select
     waybill_no
    ,apply_type_code
    ,apply_type_name
    ,apply_time
    ,status
    ,status_name
    from jms_dwd.dwd_tab_reback_transfer_express_base
    where apply_type_code = '4'
    and waybill_no is not null
  )
  , tmp_deliver_rollback_detail as (
    select
      substr(t1.input_time,1,10) as input_time
     ,t1.waybill_no
     ,t1.scan_site
     ,t1.scan_site_code
     ,t1.site_agent_code
     ,t1.site_agent_name
     ,t1.regional_desc
     ,if(t2.waybill_no is not null,1,0) as roll_back_flag  --退件标志
    from tmp_deliver t1
    left join tmp_rolback t2
           on t1.waybill_no = t2.waybill_no
  )

  select
     input_time
    ,regional_desc
    ,site_agent_name
    ,deliver_cnt
    ,roll_back_cnt
    ,roll_back_cnt/deliver_cnt as roll_back_rate
  from (
    select
       input_time
      ,max(regional_desc)   as regional_desc  --大区名称
      ,max(site_agent_name) as site_agent_name
      ,site_agent_code
      ,count(waybill_no)    as deliver_cnt
      ,sum(roll_back_flag)  as roll_back_cnt
    from tmp_deliver_rollback_detail
    group by site_agent_code,input_time
  ) t
  ;
 " >>/tmp/${name}_${dt}.txt
}

# 替换分隔符，并输出csv
cat /tmp/${name}_${dt}.txt | tr -s '\t' ',' > /tmp/${name}_${dt}.csv
# 压缩并删除 csv 文件
zip -jrm /tmp/${name}_${dt}.zip /tmp/${name}_${dt}.csv
# 上传文件
/usr/local/python3/bin/python3  /usr/local/airflow/dags/utils/operators/upload_oss.py ${name}/${name}_${dt}.zip /tmp/${name}_${dt}.zip
# zip 文件将在进程退出后被自动清理

#声明文件大小
data_size=$(ls -lh /tmp/${name}_${dt}.zip | awk '{print $5}')
#打印文件信息
Message="${name}_${dt}.zip 上传成功 大小: $data_size"
#配置钉钉告警
curl 'https://oapi.dingtalk.com/robot/send?access_token=816a8f35fd84432ffa6bff9d7714abc322628bb4a8ded86908550d2335aba613' \
   -H 'Content-Type: application/json' \
   -d "
  {\"msgtype\": \"text\",
    \"text\": {
        \"content\": \"监控内容:$Message\"
     }
  }" || true
rm -f  /tmp/${name}_${dt}.txt